会话概念：用户的一次会话含义是指用户进入系统开始到用户离开算作一次会话，
离开或者重新开始一次会话的概念是指用户的两次行为事件差值大于30分钟，
判断条件是只要两次时间差值大于30分钟就属于两次会话。
-- 1).想算两次行为的时间差，就需要算当前行的数据，与上一行或者下一行的数据，可以用lag函数实现
Select user_id, click_time,
      unix_timestamp(click_time)/60 -
      unix_timestamp(lag(click_time) over(PARTITION BY user_id ORDER BY click_time))/60
      diff
from user_clicklog
-- 2).判断条件是只要两次时间差值大于30分钟就属于两次会话,通过flag做个标记
WITH tmp as(
Select user_id, click_time,
      unix_timestamp(click_time)/60 -
      unix_timestamp(lag(click_time) over(PARTITION BY user_id ORDER BY click_time))/60
      diff
from user_clicklog)
SELECT user_id,click_time,
       case when diff>30 then 1
            else 0 end flag
 from tmp
-- 3).根据user_id进行分区，然后算出flag的累加和
WITH tmp as(
Select user_id, click_time,
      unix_timestamp(click_time)/60 -
      unix_timestamp(lag(click_time) over(PARTITION BY user_id ORDER BY click_time))/60
      diff
from user_clicklog)
select user_id, click_time,
       sum(flag) over(PARTITION BY user_id ORDER BY click_time) sum_flag
  from (SELECT user_id,click_time,
               case when diff>30 then 1
                    else 0 end flag
        from tmp) tmp2
-- 4).最后根据user_id,sum_flag,进行分区，并且得到行号
WITH tmp as(
Select user_id, click_time,
      unix_timestamp(click_time)/60 -
      unix_timestamp(lag(click_time) over(PARTITION BY user_id ORDER BY click_time))/60
      diff
from user_clicklog)
select user_id, click_time,
       row_number() OVER(PARTITION BY user_id, sum_flag ORDER BY click_time) row_num
  from(select user_id, click_time,
              sum(flag) over(PARTITION BY user_id ORDER BY click_time) sum_flag
        from (SELECT user_id,click_time,
                     case when diff>30 then 1
                          else 0 end flag
              from tmp) tmp2) tmp3